<!DOCTYPE html><html><head><title>MySQL学习笔记（Day007：多实例下/SSL）</title><meta charset='utf-8'><link href='https://dn-maxiang.qbox.me/res-min/themes/marxico.css' rel='stylesheet'></head><body><div id='preview-contents' class='note-content'>
                        <div id="wmd-preview" class="preview-content"></div>
                    <div id="wmd-preview-section-1" class="wmd-preview-section preview-content">

</div><div id="wmd-preview-section-2" class="wmd-preview-section preview-content">

<h1 id="mysql学习笔记day007多实例下ssl">MySQL学习笔记（Day007：多实例下/SSL）</h1>

<p><p class="note-tags "><code class="notebook">MySQL学习</code> </p></p>

<div><div class="toc"><div class="toc">
<ul>
<li><a href="#mysql学习笔记day007多实例下ssl">MySQL学习笔记（Day007：多实例下/SSL）</a><ul>
<li><a href="#一-多实例安装-多版本">一. 多实例安装 – 多版本</a><ul>
<li><a href="#1-mysqldmulti标签">1. [mysqld_multi]标签</a></li>
<li><a href="#2-停止mysql实例">2. 停止mysql实例</a></li>
<li><a href="#3-多实例安装-多版本">3. 多实例安装 – 多版本</a></li>
</ul>
</li>
<li><a href="#二-ssl安装">二. SSL安装</a><ul>
<li><a href="#1-开启ssl-579">1. 开启SSL (5.7.9)</a></li>
<li><a href="#2-开启证书认证579">2. 开启证书认证(5.7.9)</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</div>
</div>

</div><div id="wmd-preview-section-3" class="wmd-preview-section preview-content">

<h2 id="一-多实例安装-多版本">一. 多实例安装 – 多版本</h2>

</div><div id="wmd-preview-section-4" class="wmd-preview-section preview-content">

<h3 id="1-mysqldmulti标签">1. [mysqld_multi]标签</h3>

<ul><li><strong><code>[mysqld_multi]</code> 是否需要配置</strong> <br>
从操作演示来看，在<code>my.cnf</code>(<em>老师给的模板配置</em>)上直接配置<code>[mysqld1]</code>、<code>[mysqld2]</code>等实例标签，而<code>不配置[mysqld_multi]</code>,使用<code>mysqld_multi start 1</code>也是<code>可以启动</code>数据库实例的，但是没有<code>mysqld_safe</code>的守护进程。所以该标签<code>需要配置</code></li>
</ul>

</div><div id="wmd-preview-section-5" class="wmd-preview-section preview-content">

<h3 id="2-停止mysql实例">2. 停止mysql实例</h3>

<ul><li><p><strong><code>multi_admin用户</code>的作用</strong> <br>
通过<a href="http://dev.mysql.com/doc/refman/5.7/en/mysqld-multi.html" target="_blank">官方文档</a>中我们看到，<code>'multi_admin'@'localhost'</code>这个用户主要的作用是用来<code>关闭</code>数据库实例，因为文档中只授权了<code>SHUTDOWN</code>权限。所以在<code>[mysqld_multi]</code>标签下，我们需要配置<code>user</code>和<code>password</code><strong>(注意<code>5.7.9</code>中是<code>pass</code>)</strong>来进行关闭数据库实例。</p></li>
<li><p><strong><code>[client]</code>标签</strong> <br>
从操作演示来看，老师并没有在<code>[mysqld_multi]</code>下配置<code>user</code>和<code>password</code>，但是仍然可以关闭数据库，原因是因为<code>/root/.my.cnf</code>中存在了<code>[client]</code>标签。该标签下的用户<code>user = root</code>有关闭数据库实例的权限，因此可以关闭数据库。</p></li>
</ul>

<blockquote>
  <p>如果在<code>[client]</code>和<code>[mysqld_multi]</code>标签中同时存在<code>user</code>和<code>password</code>, 则在关闭数据库实例中会使用<code>[mysqld_multi]</code>中的<code>user</code>去关闭。 <br>
  <strong>(<code>存在精确匹配的标签，则优先使用精确匹配标签下的配置项</code>)</strong></p>
</blockquote>

</div><div id="wmd-preview-section-6" class="wmd-preview-section preview-content">

<h3 id="3-多实例安装-多版本">3. 多实例安装 – 多版本</h3>

<ul><li><p><strong>环境说明</strong></p>

<ul>
<li><code>mysqld1</code> – MySQL 5.7.9</li>
<li><code>mysqld2</code> – MySQL 5.7.9</li>
<li><code>mysqld3</code> – MySQL 5.6.27</li>
<li><code>mysqld4</code> – MySQL 5.6.27</li></ul></li>
<li><p><strong>配置文件</strong></p></li>
</ul>

</div><div id="wmd-preview-section-7" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">[client]<br>user = root<br>password = <span class="hljs-number">123</span><br><br>[mysqld_multi]  <span class="hljs-comment"># 这里使用了client标签中的user，故这里不再定义user</span><br>mysqld = /usr/<span class="hljs-built_in">local</span>/mysql/bin/mysqld_safe<br><span class="hljs-built_in">log</span> = /var/<span class="hljs-built_in">log</span>/mysqld_multi.log<br><br>[mysqld1]<br>server-id = <span class="hljs-number">11</span><br>datadir = /data1<br>basedir = /usr/<span class="hljs-built_in">local</span>/mysql   <span class="hljs-comment"># basedir定义使用了5.7的mysql版本</span><br>port = <span class="hljs-number">3307</span><br>socket = /tmp/mysql.sock1<br><br>[mysqld2]<br>server-id = <span class="hljs-number">22</span><br>datadir = /data2<br>basedir = /usr/<span class="hljs-built_in">local</span>/mysql<br>port = <span class="hljs-number">3308</span><br>socket = /tmp/mysql.sock2<br><br>[mysqld3]<br>server-id = <span class="hljs-number">33</span><br>datadir = /data3<br>basedir = /usr/<span class="hljs-built_in">local</span>/mysql56  <span class="hljs-comment"># basedir定义了使用5.6的mysql版本</span><br>port = <span class="hljs-number">3309</span><br>socket = /tmp/mysql.sock3<br>plugin_dir=/usr/<span class="hljs-built_in">local</span>/mysql56/lib/plugin  <span class="hljs-comment"># plugin 目录也变了</span><br><br><span class="hljs-comment">#这里无需特别配置mysqld, 可以继承使用[mysqld_multi]中的配置，然后根据basedir找到对应的mysqld</span><br><br>[mysqld4]<br>server-id = <span class="hljs-number">44</span><br>datadir = /data4<br>basedir = /usr/<span class="hljs-built_in">local</span>/mysql56<br>port = <span class="hljs-number">3310</span><br>socket = /tmp/mysql.sock4<br>plugin_dir=/usr/<span class="hljs-built_in">local</span>/mysql56/lib/plugin<br><br><span class="hljs-comment">#--------------以下参数是老师的模板，只是将个别size调小-----------</span><br>[mysqld]<br><span class="hljs-comment">########basic settings########</span><br>server-id = <span class="hljs-number">100</span><br>port = <span class="hljs-number">3306</span><br>user = mysql<br><span class="hljs-built_in">bind</span>_address = <span class="hljs-number">0.0</span>.<span class="hljs-number">0.0</span><br><span class="hljs-comment">#autocommit = 0</span><br>character_<span class="hljs-built_in">set</span>_server=utf8mb4<br>skip_name_resolve = <span class="hljs-number">1</span><br>max_connections = <span class="hljs-number">800</span><br>max_connect_errors = <span class="hljs-number">1000</span><br>datadir = /data/mysql_data<br>transaction_isolation = READ-COMMITTED<br>explicit_defaults_<span class="hljs-keyword">for</span>_timestamp = <span class="hljs-number">1</span><br>join_buffer_size = <span class="hljs-number">134217728</span><br>tmp_table_size = <span class="hljs-number">67108864</span><br>tmpdir = /tmp<br>max_allowed_packet = <span class="hljs-number">16777216</span><br>sql_mode = <span class="hljs-string">"STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"</span><br>interactive_timeout = <span class="hljs-number">1800</span><br><span class="hljs-built_in">wait</span>_timeout = <span class="hljs-number">1800</span><br><span class="hljs-built_in">read</span>_buffer_size = <span class="hljs-number">16777216</span><br><span class="hljs-built_in">read</span>_rnd_buffer_size = <span class="hljs-number">33554432</span><br>sort_buffer_size = <span class="hljs-number">33554432</span><br><span class="hljs-comment">########log settings########</span><br><span class="hljs-built_in">log</span>_error = error.log<br>slow_query_<span class="hljs-built_in">log</span> = <span class="hljs-number">1</span><br>slow_query_<span class="hljs-built_in">log</span>_file = slow.log<br><span class="hljs-built_in">log</span>_queries_not_using_indexes = <span class="hljs-number">1</span><br><span class="hljs-built_in">log</span>_slow_admin_statements = <span class="hljs-number">1</span><br><span class="hljs-built_in">log</span>_slow_slave_statements = <span class="hljs-number">1</span><br><span class="hljs-built_in">log</span>_throttle_queries_not_using_indexes = <span class="hljs-number">10</span><br>expire_logs_days = <span class="hljs-number">90</span><br>long_query_time = <span class="hljs-number">2</span><br>min_examined_row_<span class="hljs-built_in">limit</span> = <span class="hljs-number">100</span><br><span class="hljs-comment">########replication settings########</span><br>master_info_repository = TABLE<br>relay_<span class="hljs-built_in">log</span>_info_repository = TABLE<br><span class="hljs-built_in">log</span>_bin = bin.log<br>sync_binlog = <span class="hljs-number">1</span><br>gtid_mode = on<br>enforce_gtid_consistency = <span class="hljs-number">1</span><br><span class="hljs-built_in">log</span>_slave_updates<br>binlog_format = row <br>relay_<span class="hljs-built_in">log</span> = relay.log<br>relay_<span class="hljs-built_in">log</span>_recovery = <span class="hljs-number">1</span><br>binlog_gtid_simple_recovery = <span class="hljs-number">1</span><br>slave_skip_errors = ddl_exist_errors<br><span class="hljs-comment">########innodb settings########</span><br>innodb_page_size = <span class="hljs-number">8192</span><br>innodb_buffer_pool_size = <span class="hljs-number">1</span>G    <span class="hljs-comment"># 该参数减小到1G</span><br>innodb_buffer_pool_instances = <span class="hljs-number">8</span><br>innodb_buffer_pool_load_at_startup = <span class="hljs-number">1</span><br>innodb_buffer_pool_dump_at_shutdown = <span class="hljs-number">1</span><br>innodb_lru_scan_depth = <span class="hljs-number">2000</span><br>innodb_lock_<span class="hljs-built_in">wait</span>_timeout = <span class="hljs-number">5</span><br>innodb_io_capacity = <span class="hljs-number">4000</span><br>innodb_io_capacity_max = <span class="hljs-number">8000</span><br>innodb_flush_method = O_DIRECT<br>innodb_file_format = Barracuda<br>innodb_file_format_max = Barracuda<br><span class="hljs-comment">#innodb_log_group_home_dir = /redolog/</span><br><span class="hljs-comment">#innodb_undo_directory = /undolog/</span><br>innodb_undo_logs = <span class="hljs-number">128</span><br>innodb_undo_tablespaces = <span class="hljs-number">3</span><br>innodb_flush_neighbors = <span class="hljs-number">1</span><br>innodb_<span class="hljs-built_in">log</span>_file_size = <span class="hljs-number">128</span>M  <span class="hljs-comment"># 该参数减小到 128M</span><br>innodb_<span class="hljs-built_in">log</span>_buffer_size = <span class="hljs-number">16777216</span><br>innodb_purge_threads = <span class="hljs-number">4</span><br>innodb_large_prefix = <span class="hljs-number">1</span><br>innodb_thread_concurrency = <span class="hljs-number">64</span><br>innodb_<span class="hljs-built_in">print</span>_all_deadlocks = <span class="hljs-number">1</span><br>innodb_strict_mode = <span class="hljs-number">1</span><br>innodb_sort_buffer_size = <span class="hljs-number">67108864</span> <br><span class="hljs-comment">########semi sync replication settings########</span><br>plugin_dir=/usr/<span class="hljs-built_in">local</span>/mysql/lib/plugin<br><span class="hljs-comment">#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"</span><br>loose_rpl_semi_sync_master_enabled = <span class="hljs-number">1</span><br>loose_rpl_semi_sync_slave_enabled = <span class="hljs-number">1</span><br>loose_rpl_semi_sync_master_timeout = <span class="hljs-number">5000</span><br><br>[mysqld-<span class="hljs-number">5.7</span>]<br>innodb_buffer_pool_dump_pct = <span class="hljs-number">40</span><br>innodb_page_cleaners = <span class="hljs-number">4</span><br>innodb_undo_<span class="hljs-built_in">log</span>_truncate = <span class="hljs-number">1</span><br>innodb_max_undo_<span class="hljs-built_in">log</span>_size = <span class="hljs-number">1</span>G   <span class="hljs-comment"># 该参数减小到1G</span><br>innodb_purge_rseg_truncate_frequency = <span class="hljs-number">128</span><br>binlog_gtid_simple_recovery=<span class="hljs-number">1</span><br><span class="hljs-built_in">log</span>_timestamps=system<br>transaction_write_<span class="hljs-built_in">set</span>_extraction=MURMUR32<br>show_compatibility_56=on<br></code></pre>

<blockquote>
  <p><strong>注意MySQL5.6.27的<code>plugin_dir</code>的路径</strong></p>
  
  <p>配置说明： <br>
  1：配置的标签顺序没有关系，不会影响最终配置的有效性。 <br>
  2：同类型标签中的配置项会合并，形成一个大的配置项 <br>
  2：<code>匹配度高</code>的标签中的配置项的<code>值</code>，会<code>覆盖</code>掉<code>匹配度低</code>的标签中的配置项的<code>值</code></p>
  
  <p><strong>[mysqld<code>N</code>]中的配置项会和[mysqld]中的配置项进行合并，并且[mysqld<code>N</code>]中已有的配置项的值，会覆盖掉[mysqld]中的配置项的值,如<code>datadir</code>, <code>port</code>等</strong></p>
</blockquote>

<ul><li><strong>安装操作</strong></li>
</ul>

</div><div id="wmd-preview-section-8" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment">#</span><br><span class="hljs-comment"># 准备好数据目录，并初始化安装</span><br><span class="hljs-comment">#</span><br>[root@MyServer ~]&gt; mkdir /data1<br>[root@MyServer ~]&gt; mkdir /data2<br>[root@MyServer ~]&gt; mkdir /data3<br>[root@MyServer ~]&gt; mkdir /data4<br>[root@MyServer ~]&gt; chown mysql.mysql /data{<span class="hljs-number">1</span>..<span class="hljs-number">4</span>}<br>[root@MyServer ~]&gt; mysqld --initialize --user=mysql --datadir=/data1<br><span class="hljs-comment">#</span><br><span class="hljs-comment"># 这里无输出，临时密码见 /data1/error.log</span><br><span class="hljs-comment">#</span><br>[root@MyServer ~]&gt; mysqld --initialize --user=mysql --datadir=/data2<br><span class="hljs-comment">#</span><br><span class="hljs-comment"># 这里无输出，临时密码见 /data1/error.log</span><br><span class="hljs-comment">#</span><br>[root@MyServer mysql56]&gt; <span class="hljs-built_in">pwd</span><br>/usr/<span class="hljs-built_in">local</span>/mysql56<br>[root@MyServer mysql56]&gt; scripts/mysql_install_db  --user=mysql --datadir=/data3<br><span class="hljs-comment">#</span><br><span class="hljs-comment"># 这里有部分信息输出</span><br><span class="hljs-comment"># 安装后，需要检查error.log 确保没有错误出现</span><br><span class="hljs-comment"># 注意使用空密码登录后，修改密码</span><br><span class="hljs-comment">#</span><br>[root@MyServer mysql56]&gt; scripts/mysql_install_db  --user=mysql --datadir=/data4<br><span class="hljs-comment">#</span><br><span class="hljs-comment"># 这里有部分信息输出</span><br><span class="hljs-comment"># 安装后，需要检查error.log 确保没有错误出现</span><br><span class="hljs-comment"># 注意使用空密码登录后，修改密码</span><br><span class="hljs-comment">#</span><br>[root@MyServer ~]&gt; cp /usr/<span class="hljs-built_in">local</span>/mysql/support-files/mysqld_multi.server  /etc/init.d/mysqld_multid <br><span class="hljs-comment"># 拷贝启动脚本，方便自启</span><br>[root@MyServer ~]&gt; chkconfig mysqld_multid on<br><br>[root@MyServer ~]&gt; mysqld_multi report                       <br>Reporting MySQL servers<br>MySQL server from group: mysqld1 is not running<br>MySQL server from group: mysqld2 is not running<br>MySQL server from group: mysqld3 is not running<br>MySQL server from group: mysqld4 is not running<br><br>[root@MyServer ~]&gt; mysqld_multi  report<br>Reporting MySQL servers<br>MySQL server from group: mysqld1 is running<br>MySQL server from group: mysqld2 is running<br>MySQL server from group: mysqld3 is running<br>MySQL server from group: mysqld4 is running<br><br>[root@MyServer ~]&gt; ps -ef | grep mysqld<br>root     <span class="hljs-number">13859</span>     <span class="hljs-number">1</span>  <span class="hljs-number">0</span> <span class="hljs-number">22</span>:<span class="hljs-number">35</span> pts/<span class="hljs-number">1</span>    <span class="hljs-number">00</span>:<span class="hljs-number">00</span>:<span class="hljs-number">00</span> /bin/sh /usr/<span class="hljs-built_in">local</span>/mysql/bin/mysqld_safe --server-id=<span class="hljs-number">11</span> --datadir=/data1 --basedir=/usr/<span class="hljs-built_in">local</span>/mysql --port=<span class="hljs-number">3307</span> --socket=/tmp/mysql.sock1<br>root     <span class="hljs-number">13865</span>     <span class="hljs-number">1</span>  <span class="hljs-number">0</span> <span class="hljs-number">22</span>:<span class="hljs-number">35</span> pts/<span class="hljs-number">1</span>    <span class="hljs-number">00</span>:<span class="hljs-number">00</span>:<span class="hljs-number">00</span> /bin/sh /usr/<span class="hljs-built_in">local</span>/mysql/bin/mysqld_safe --server-id=<span class="hljs-number">22</span> --datadir=/data2 --basedir=/usr/<span class="hljs-built_in">local</span>/mysql --port=<span class="hljs-number">3308</span> --socket=/tmp/mysql.sock2<br>root     <span class="hljs-number">13872</span>     <span class="hljs-number">1</span>  <span class="hljs-number">0</span> <span class="hljs-number">22</span>:<span class="hljs-number">35</span> pts/<span class="hljs-number">1</span>    <span class="hljs-number">00</span>:<span class="hljs-number">00</span>:<span class="hljs-number">00</span> /bin/sh /usr/<span class="hljs-built_in">local</span>/mysql/bin/mysqld_safe --server-id=<span class="hljs-number">33</span> --datadir=/data3 --basedir=/usr/<span class="hljs-built_in">local</span>/mysql56 --port=<span class="hljs-number">3309</span> --socket=/tmp/mysql.sock3 --plugin_dir=/usr/<span class="hljs-built_in">local</span>/mysql56/lib/plugin<br>root     <span class="hljs-number">13886</span>     <span class="hljs-number">1</span>  <span class="hljs-number">0</span> <span class="hljs-number">22</span>:<span class="hljs-number">35</span> pts/<span class="hljs-number">1</span>    <span class="hljs-number">00</span>:<span class="hljs-number">00</span>:<span class="hljs-number">00</span> /bin/sh /usr/<span class="hljs-built_in">local</span>/mysql/bin/mysqld_safe --server-id=<span class="hljs-number">44</span> --datadir=/data4 --basedir=/usr/<span class="hljs-built_in">local</span>/mysql56 --port=<span class="hljs-number">3310</span> --socket=/tmp/mysql.sock4 --plugin_dir=/usr/<span class="hljs-built_in">local</span>/mysql56/lib/plugin<br><span class="hljs-comment">#</span><br><span class="hljs-comment"># 上面是mysqld_safe的守护进程</span><br><span class="hljs-comment"># 下面是实际的mysqld的进程，观察mysqld的路径</span><br><span class="hljs-comment"># 因为指定了basedir，所以会自动识别mysqld的路径</span><br><span class="hljs-comment">#</span><br>mysql    <span class="hljs-number">17783</span> <span class="hljs-number">13859</span>  <span class="hljs-number">0</span> <span class="hljs-number">22</span>:<span class="hljs-number">35</span> pts/<span class="hljs-number">1</span>    <span class="hljs-number">00</span>:<span class="hljs-number">00</span>:<span class="hljs-number">00</span> /usr/<span class="hljs-built_in">local</span>/mysql-<span class="hljs-number">5.7</span>.<span class="hljs-number">9</span>-linux-glibc2.<span class="hljs-number">5</span>-x86_64/bin/mysqld --basedir=/usr/<span class="hljs-built_in">local</span>/mysql --datadir=/data1 --plugin-dir=/usr/<span class="hljs-built_in">local</span>/mysql/lib/plugin --user=mysql --server-id=<span class="hljs-number">11</span> --log-error=/data1/error.log --pid-file=/data1/MyServer.pid --socket=/tmp/mysql.sock1 --port=<span class="hljs-number">3307</span><br>mysql    <span class="hljs-number">17784</span> <span class="hljs-number">13865</span>  <span class="hljs-number">0</span> <span class="hljs-number">22</span>:<span class="hljs-number">35</span> pts/<span class="hljs-number">1</span>    <span class="hljs-number">00</span>:<span class="hljs-number">00</span>:<span class="hljs-number">00</span> /usr/<span class="hljs-built_in">local</span>/mysql-<span class="hljs-number">5.7</span>.<span class="hljs-number">9</span>-linux-glibc2.<span class="hljs-number">5</span>-x86_64/bin/mysqld --basedir=/usr/<span class="hljs-built_in">local</span>/mysql --datadir=/data2 --plugin-dir=/usr/<span class="hljs-built_in">local</span>/mysql/lib/plugin --user=mysql --server-id=<span class="hljs-number">22</span> --log-error=/data2/error.log --pid-file=/data2/MyServer.pid --socket=/tmp/mysql.sock2 --port=<span class="hljs-number">3308</span><br>mysql    <span class="hljs-number">17819</span> <span class="hljs-number">13872</span>  <span class="hljs-number">0</span> <span class="hljs-number">22</span>:<span class="hljs-number">35</span> pts/<span class="hljs-number">1</span>    <span class="hljs-number">00</span>:<span class="hljs-number">00</span>:<span class="hljs-number">00</span> /usr/<span class="hljs-built_in">local</span>/mysql-<span class="hljs-number">5.6</span>.<span class="hljs-number">27</span>-linux-glibc2.<span class="hljs-number">5</span>-x86_64/bin/mysqld --basedir=/usr/<span class="hljs-built_in">local</span>/mysql56 --datadir=/data3 --plugin-dir=/usr/<span class="hljs-built_in">local</span>/mysql56/lib/plugin --user=mysql --server-id=<span class="hljs-number">33</span> --log-error=/data3/error.log --pid-file=/data3/MyServer.pid --socket=/tmp/mysql.sock3 --port=<span class="hljs-number">3309</span><br>mysql    <span class="hljs-number">17824</span> <span class="hljs-number">13886</span>  <span class="hljs-number">0</span> <span class="hljs-number">22</span>:<span class="hljs-number">35</span> pts/<span class="hljs-number">1</span>    <span class="hljs-number">00</span>:<span class="hljs-number">00</span>:<span class="hljs-number">00</span> /usr/<span class="hljs-built_in">local</span>/mysql-<span class="hljs-number">5.6</span>.<span class="hljs-number">27</span>-linux-glibc2.<span class="hljs-number">5</span>-x86_64/bin/mysqld --basedir=/usr/<span class="hljs-built_in">local</span>/mysql56 --datadir=/data4 --plugin-dir=/usr/<span class="hljs-built_in">local</span>/mysql56/lib/plugin --user=mysql --server-id=<span class="hljs-number">44</span> --log-error=/data4/error.log --pid-file=/data4/MyServer.pid --socket=/tmp/mysql.sock4 --port=<span class="hljs-number">3310</span><br>root     <span class="hljs-number">17988</span>  <span class="hljs-number">2657</span>  <span class="hljs-number">0</span> <span class="hljs-number">22</span>:<span class="hljs-number">44</span> pts/<span class="hljs-number">1</span>    <span class="hljs-number">00</span>:<span class="hljs-number">00</span>:<span class="hljs-number">00</span> grep mysqld<br><br><br>[root@MyServer ~]&gt; ps -ef | grep mysqld |  grep -v mysqld_safe | grep -v grep | awk <span class="hljs-string">'{print $8" "$9}'</span>  <br>/usr/<span class="hljs-built_in">local</span>/mysql-<span class="hljs-number">5.7</span>.<span class="hljs-number">9</span>-linux-glibc2.<span class="hljs-number">5</span>-x86_64/bin/mysqld --basedir=/usr/<span class="hljs-built_in">local</span>/mysql<br>/usr/<span class="hljs-built_in">local</span>/mysql-<span class="hljs-number">5.7</span>.<span class="hljs-number">9</span>-linux-glibc2.<span class="hljs-number">5</span>-x86_64/bin/mysqld --basedir=/usr/<span class="hljs-built_in">local</span>/mysql<br>/usr/<span class="hljs-built_in">local</span>/mysql-<span class="hljs-number">5.6</span>.<span class="hljs-number">27</span>-linux-glibc2.<span class="hljs-number">5</span>-x86_64/bin/mysqld --basedir=/usr/<span class="hljs-built_in">local</span>/mysql56<br>/usr/<span class="hljs-built_in">local</span>/mysql-<span class="hljs-number">5.6</span>.<span class="hljs-number">27</span>-linux-glibc2.<span class="hljs-number">5</span>-x86_64/bin/mysqld --basedir=/usr/<span class="hljs-built_in">local</span>/mysql56<br></code></pre>

<blockquote>
  <p><code>mysql3</code> 和 <code>mysql4</code>初始状态没有密码，以前可以直接使用<code>mysql -S  mysql.sock</code>登录，而现在登录的时候特别注意，因为我们使用了<code>[client]</code>标签,登录的时候如果不加<code>-p</code>参数会默认使用标签下的<code>user</code>和<code>password</code>, 然后导致登录不进去，所以需要使用如下登录方式：</p>
</blockquote>

</div><div id="wmd-preview-section-9" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">shell&gt; mysql -u root -P3309 -S /tmp/mysql.sock3 -p<br>Enter password: [直接回车]<br>elcome to the MySQL monitor.  Commands end with ; or \g.<br>Your MySQL connection id is <span class="hljs-number">3</span><br>Server version: <span class="hljs-number">5.6</span>.<span class="hljs-number">27</span>-log MySQL Community Server (GPL)<br><br>Copyright (c) <span class="hljs-number">2000</span>, <span class="hljs-number">2015</span>, Oracle and/or its affiliates. All rights reserved.<br><br>Oracle is a registered trademark of Oracle Corporation and/or its<br>affiliates. Other names may be trademarks of their respective<br>owners.<br><br>Type <span class="hljs-string">'help;'</span> or <span class="hljs-string">'\h'</span> <span class="hljs-keyword">for</span> help. Type <span class="hljs-string">'\c'</span> to clear the current input statement.<br><br>mysql&gt; <span class="hljs-built_in">set</span> password = password(<span class="hljs-string">"123"</span>); <span class="hljs-comment">#进行修改密码</span><br></code></pre>

<ul><li><strong>设置login-path</strong> <br>
设置<code>login-path</code>主要为了能够简化登录，同时还可以让每个数据库的密码都不同，避免使用[client]下的统一用户名密码</li>
</ul>

</div><div id="wmd-preview-section-10" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">[root@MyServer ~]&gt; mysql_config_editor  <span class="hljs-built_in">set</span> -G mysql1 -u root -p -S /tmp/mysql.sock1<br>[root@MyServer ~]&gt; mysql_config_editor  <span class="hljs-built_in">set</span> -G mysql2 -u root -p -S /tmp/mysql.sock2<br>[root@MyServer ~]&gt; mysql_config_editor  <span class="hljs-built_in">set</span> -G mysql3 -u root -p -S /tmp/mysql.sock3<br>[root@MyServer ~]&gt; mysql_config_editor  <span class="hljs-built_in">set</span> -G mysql4 -u root -p -S /tmp/mysql.sock4<br><br><span class="hljs-comment"># 然后可以使用mysql --login-path=mysql1 这种方式登录</span><br></code></pre>

</div><div id="wmd-preview-section-11" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- mysql1 </span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-keyword">version</span>();</span><br>+<span class="hljs-comment">-----------+</span><br>| version() |<br>+<span class="hljs-comment">-----------+</span><br>| 5.7.9-log |<br>+<span class="hljs-comment">-----------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.01</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"port"</span>;</span> <br>+<span class="hljs-comment">---------------+-------+</span><br>| Variable_name | Value |<br>+<span class="hljs-comment">---------------+-------+</span><br>| port          | 3307  |<br>+<span class="hljs-comment">---------------+-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- mysql2</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">version</span>();</span><br>+<span class="hljs-comment">-----------+</span><br>| version() |<br>+<span class="hljs-comment">-----------+</span><br>| 5.7.9-log |<br>+<span class="hljs-comment">-----------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"port"</span>;</span><br>+<span class="hljs-comment">---------------+-------+</span><br>| Variable_name | Value |<br>+<span class="hljs-comment">---------------+-------+</span><br>| port          | 3308  |<br>+<span class="hljs-comment">---------------+-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- mysql3</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">version</span>();</span><br>+<span class="hljs-comment">------------+</span><br>| version()  |<br>+<span class="hljs-comment">------------+</span><br>| 5.6.27-log |  <span class="hljs-comment">-- mysql 5.6.27 </span><br>+<span class="hljs-comment">------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"port"</span>;</span><br>+<span class="hljs-comment">---------------+-------+</span><br>| Variable_name | Value |<br>+<span class="hljs-comment">---------------+-------+</span><br>| port          | 3309  |<br>+<span class="hljs-comment">---------------+-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- mysql4</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">version</span>();</span><br>+<span class="hljs-comment">------------+</span><br>| version()  |<br>+<span class="hljs-comment">------------+</span><br>| 5.6.27-log |  <span class="hljs-comment">-- mysql 5.6.27</span><br>+<span class="hljs-comment">------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"port"</span>;</span><br>+<span class="hljs-comment">---------------+-------+</span><br>| Variable_name | Value |<br>+<span class="hljs-comment">---------------+-------+</span><br>| port          | 3310  |<br>+<span class="hljs-comment">---------------+-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

</div><div id="wmd-preview-section-12" class="wmd-preview-section preview-content">

<h2 id="二-ssl安装">二. SSL安装</h2>

<p>SSL（Secure Socket Layer）是维护Client - Server之间加密通讯的一套安全协议；</p>

</div><div id="wmd-preview-section-13" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"port"</span>;</span><br>+<span class="hljs-comment">---------------+-------+</span><br>| Variable_name | Value |<br>+<span class="hljs-comment">---------------+-------+</span><br>| port          | 3307  |<br>+<span class="hljs-comment">---------------+-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"%ssl%"</span>;</span><br>+<span class="hljs-comment">---------------+----------+</span><br>| Variable_name | Value    |<br>+<span class="hljs-comment">---------------+----------+</span><br>| have_openssl  | DISABLED |  <span class="hljs-comment">--  SSL被禁止了</span><br>| have_ssl      | DISABLED |<br>| ssl_ca        |          |<br>| ssl_capath    |          |<br>| ssl_cert      |          |<br>| ssl_cipher    |          |<br>| ssl_crl       |          |<br>| ssl_crlpath   |          |<br>| ssl_key       |          |<br>+<span class="hljs-comment">---------------+----------+</span><br>9 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<blockquote>
  <p>经过之前的多实例安装，是没有开启SSL配置的</p>
</blockquote>

</div><div id="wmd-preview-section-14" class="wmd-preview-section preview-content">

<h3 id="1-开启ssl-579">1. 开启SSL (5.7.9)</h3>

<p></p><ul><li>环境说明 <br>
<ul>
<li>虚拟机1：MyServer； IP：172.18.14.68, MySQL实例1 - mysql1</li>
<li>虚拟机2：MyServer； IP：172.18.14.41, MySQL客户端 <br></li></ul></li></ul><p></p>

<blockquote>
  操作过程中看到的192.168.115.223 是宿主机IP，因为使用KVM虚拟机的NAT功能，所以会被转换
  
  </blockquote></div><div id="wmd-preview-section-15" class="wmd-preview-section preview-content">


<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment">#</span><br><span class="hljs-comment"># 当前虚拟机1 MyServer</span><br><span class="hljs-comment">#</span><br>[root@MyServer mysql]&gt; <span class="hljs-built_in">pwd</span><br>/usr/<span class="hljs-built_in">local</span>/mysql<br>[root@MyServer mysql]&gt; bin/mysql_ssl_rsa_setup  --datadir=/data1 --user=mysql --uid=mysql <br><span class="hljs-comment"># 使用--uid后，就不需要chown mysql.mysql *.pem</span><br><br>[root@MyServer data1]<span class="hljs-comment"># pwd</span><br>/data1<br>[root@MyServer data1]<span class="hljs-comment"># ll | grep pem</span><br>-rw-------. <span class="hljs-number">1</span> mysql mysql      <span class="hljs-number">1675</span> Nov <span class="hljs-number">25</span> <span class="hljs-number">23</span>:<span class="hljs-number">55</span> ca-key.pem<br>-rw-r--r--. <span class="hljs-number">1</span> mysql mysql      <span class="hljs-number">1070</span> Nov <span class="hljs-number">25</span> <span class="hljs-number">23</span>:<span class="hljs-number">55</span> ca.pem<br>-rw-r--r--. <span class="hljs-number">1</span> mysql mysql      <span class="hljs-number">1078</span> Nov <span class="hljs-number">25</span> <span class="hljs-number">23</span>:<span class="hljs-number">55</span> client-cert.pem <span class="hljs-comment">#客户端证书文件</span><br>-rw-------. <span class="hljs-number">1</span> mysql mysql      <span class="hljs-number">1679</span> Nov <span class="hljs-number">25</span> <span class="hljs-number">23</span>:<span class="hljs-number">55</span> client-key.pem  <span class="hljs-comment">#客户端私钥文件</span><br>-rw-------. <span class="hljs-number">1</span> mysql mysql      <span class="hljs-number">1675</span> Nov <span class="hljs-number">25</span> <span class="hljs-number">23</span>:<span class="hljs-number">55</span> private_key.pem <span class="hljs-comment">#用于密钥交换的公钥</span><br>-rw-r--r--. <span class="hljs-number">1</span> mysql mysql       <span class="hljs-number">451</span> Nov <span class="hljs-number">25</span> <span class="hljs-number">23</span>:<span class="hljs-number">55</span> public_key.pem <span class="hljs-comment">#用户密钥交换的私钥</span><br>-rw-r--r--. <span class="hljs-number">1</span> mysql mysql      <span class="hljs-number">1078</span> Nov <span class="hljs-number">25</span> <span class="hljs-number">23</span>:<span class="hljs-number">55</span> server-cert.pem <span class="hljs-comment">#服务器端证书文件</span><br>-rw-------. <span class="hljs-number">1</span> mysql mysql      <span class="hljs-number">1679</span> Nov <span class="hljs-number">25</span> <span class="hljs-number">23</span>:<span class="hljs-number">55</span> server-key.pem <span class="hljs-comment">#服务器端私钥文件</span><br>[root@MyServer data1]&gt; mysqld_multi  stop <span class="hljs-number">1</span><br>[root@MyServer data1]&gt; mysqld_multi  start <span class="hljs-number">1</span><br></code></pre>

<blockquote>
  <p>关于几个pem文件的用途说面，见<a href="http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-5.html" target="_blank">官方文档</a>，并搜索关键字<code>private/public key-pair</code></p>
</blockquote>

</div><div id="wmd-preview-section-16" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 当前虚拟机1 MyServer ,当前实例为 mysql1</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"port"</span>;</span><br>+<span class="hljs-comment">---------------+-------+</span><br>| Variable_name | Value |<br>+<span class="hljs-comment">---------------+-------+</span><br>| port          | 3307  |<br>+<span class="hljs-comment">---------------+-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"%ssl%"</span>;</span><br>+<span class="hljs-comment">---------------+-----------------+</span><br>| Variable_name | Value           |<br>+<span class="hljs-comment">---------------+-----------------+</span><br>| have_openssl  | YES             |  <span class="hljs-comment">-- 已经支持SSL</span><br>| have_ssl      | YES             |<br>| ssl_ca        | ca.pem          |<br>| ssl_capath    |                 |<br>| ssl_cert      | server-cert.pem |  <span class="hljs-comment">-- 公钥文件</span><br>| ssl_cipher    |                 |<br>| ssl_crl       |                 |<br>| ssl_crlpath   |                 |<br>| ssl_key       | server-key.pem  |  <span class="hljs-comment">-- 私钥文件</span><br>+<span class="hljs-comment">---------------+-----------------+</span><br>9 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; \s  <span class="hljs-comment">-- status</span><br><span class="hljs-comment">--------------</span><br>mysql  Ver <span class="hljs-number">14.14</span> Distrib <span class="hljs-number">5.7</span><span class="hljs-number">.9</span>, <span class="hljs-keyword">for</span> linux-glibc2<span class="hljs-number">.5</span> (x86_64) <span class="hljs-keyword">using</span>  EditLine wrapper<br><br><span class="hljs-keyword">Connection</span> id:          <span class="hljs-number">2</span><br><span class="hljs-keyword">Current</span> <span class="hljs-keyword">database</span>:<br><span class="hljs-keyword">Current</span> <span class="hljs-keyword">user</span>:           root@localhost<br>SSL:                    <span class="hljs-keyword">Not</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">use</span>  <span class="hljs-comment">-- 此时本地socket登录，不用SSL</span><br><span class="hljs-keyword">Current</span> pager:          stdout<br><span class="hljs-keyword">Using</span> <span class="hljs-keyword">outfile</span>:          <span class="hljs-string">''</span><br><span class="hljs-keyword">Using</span> delimiter:        ;</span><br>Server version:         5.7.9-log MySQL Community Server (GPL)<br>Protocol version:       10<br>Connection:             Localhost via UNIX socket<br>Server characterset:    utf8mb4<br>Db     characterset:    utf8mb4<br>Client characterset:    utf8<br>Conn.  characterset:    utf8<br>UNIX socket:            /tmp/mysql.sock1<br>Uptime:                 6 min 16 sec<br><br>Threads: 1  Questions: 7  Slow queries: 0  Opens: 108  <span class="hljs-operator"><span class="hljs-keyword">Flush</span> <span class="hljs-keyword">tables</span>: <span class="hljs-number">1</span>  <span class="hljs-keyword">Open</span> <span class="hljs-keyword">tables</span>: <span class="hljs-number">101</span>  Queries per <span class="hljs-keyword">second</span> <span class="hljs-keyword">avg</span>: <span class="hljs-number">0.018</span><br><span class="hljs-comment">--------------</span><br><br>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">user</span> <span class="hljs-string">'burn'</span>@<span class="hljs-string">'%'</span> <span class="hljs-keyword">identified</span> <span class="hljs-keyword">by</span> <span class="hljs-string">'123'</span>;</span> <span class="hljs-comment">-- 创建一个burn@%用户，先不require ssl</span><br>Query OK, 0 rows affected (0.02 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">grant</span> <span class="hljs-keyword">all</span> <span class="hljs-keyword">on</span> *.* <span class="hljs-keyword">to</span> <span class="hljs-string">'burn'</span>@<span class="hljs-string">'%'</span>;</span><br>Query OK, 0 rows affected (0.01 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> mysql.<span class="hljs-keyword">user</span> <span class="hljs-keyword">where</span> <span class="hljs-keyword">user</span>=<span class="hljs-string">'burn'</span>\G<br>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br>                  Host: %<br>                  <span class="hljs-keyword">User</span>: burn<br>           Select_priv: Y<br>           Insert_priv: Y<br>           Update_priv: Y<br>           Delete_priv: Y<br>           Create_priv: Y<br>             Drop_priv: Y<br>           Reload_priv: Y<br>         Shutdown_priv: Y<br>          Process_priv: Y<br>             File_priv: Y<br>            Grant_priv: N<br>       References_priv: Y<br>            Index_priv: Y<br>            Alter_priv: Y<br>          Show_db_priv: Y<br>            Super_priv: Y<br> Create_tmp_table_priv: Y<br>      Lock_tables_priv: Y<br>          Execute_priv: Y<br>       Repl_slave_priv: Y<br>      Repl_client_priv: Y<br>      Create_view_priv: Y<br>        Show_view_priv: Y<br>   Create_routine_priv: Y<br>    Alter_routine_priv: Y<br>      Create_user_priv: Y<br>            Event_priv: Y<br>          Trigger_priv: Y<br>Create_tablespace_priv: Y<br>              ssl_type:         <span class="hljs-comment">--  此处为空</span><br>            ssl_cipher: <br>           x509_issuer: <br>          x509_subject: <br>         max_questions: <span class="hljs-number">0</span><br>           max_updates: <span class="hljs-number">0</span><br>       max_connections: <span class="hljs-number">0</span><br>  max_user_connections: <span class="hljs-number">0</span><br>                <span class="hljs-keyword">plugin</span>: mysql_native_password<br> authentication_string: *<span class="hljs-number">23</span>AE809DDACAF96AF0FD78ED04B6A265E05AA257<br>      password_expired: N<br> password_last_changed: <span class="hljs-number">2015</span>-<span class="hljs-number">11</span>-<span class="hljs-number">26</span> <span class="hljs-number">09</span>:<span class="hljs-number">55</span>:<span class="hljs-number">31</span><br>     password_lifetime: <span class="hljs-literal">NULL</span><br>        account_locked: N<br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

</div><div id="wmd-preview-section-17" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment">#</span><br><span class="hljs-comment"># 当前虚拟机2  MyServer2</span><br><span class="hljs-comment">#</span><br>[root@MyServer2 bin]&gt; ./mysql -u burn -h <span class="hljs-number">172.18</span>.<span class="hljs-number">14.68</span> -P3307 -p<br>Enter password: <br>Welcome to the MySQL monitor.  Commands end with ; or \g.<br>Your MySQL connection id is <span class="hljs-number">6</span><br>Server version: <span class="hljs-number">5.7</span>.<span class="hljs-number">9</span>-log MySQL Community Server (GPL)<br><br>Copyright (c) <span class="hljs-number">2000</span>, <span class="hljs-number">2015</span>, Oracle and/or its affiliates. All rights reserved.<br><br>Oracle is a registered trademark of Oracle Corporation and/or its<br>affiliates. Other names may be trademarks of their respective<br>owners.<br><br>Type <span class="hljs-string">'help;'</span> or <span class="hljs-string">'\h'</span> <span class="hljs-keyword">for</span> help. Type <span class="hljs-string">'\c'</span> to clear the current input statement.<br><br>mysql&gt; \s<br>--------------<br>./mysql  Ver <span class="hljs-number">14.14</span> Distrib <span class="hljs-number">5.7</span>.<span class="hljs-number">9</span>, <span class="hljs-keyword">for</span> linux-glibc2.<span class="hljs-number">5</span> (x86_64) using  EditLine wrapper<br><br>Connection id:          <span class="hljs-number">6</span><br>Current database:<br>Current user:           burn@<span class="hljs-number">192.168</span>.<span class="hljs-number">115.223</span><br>SSL:                    Cipher <span class="hljs-keyword">in</span> use is DHE-RSA-AES256-SHA  <span class="hljs-comment">#已经使用了ssl登录了</span><br>Current pager:          stdout<br>Using outfile:          <span class="hljs-string">''</span><br>Using delimiter:        ;<br>Server version:         <span class="hljs-number">5.7</span>.<span class="hljs-number">9</span>-log MySQL Community Server (GPL)<br>Protocol version:       <span class="hljs-number">10</span><br>Connection:             <span class="hljs-number">172.18</span>.<span class="hljs-number">14.68</span> via TCP/IP<br>Server characterset:    utf8mb4<br>Db     characterset:    utf8mb4<br>Client characterset:    utf8<br>Conn.  characterset:    utf8<br>TCP port:               <span class="hljs-number">3307</span><br>Uptime:                 <span class="hljs-number">3</span> min <span class="hljs-number">6</span> sec<br><br>Threads: <span class="hljs-number">2</span>  Questions: <span class="hljs-number">19</span>  Slow queries: <span class="hljs-number">0</span>  Opens: <span class="hljs-number">109</span>  Flush tables: <span class="hljs-number">1</span>  Open tables: <span class="hljs-number">102</span>  Queries per second avg: <span class="hljs-number">0.102</span><br>--------<br></code></pre>

</div><div id="wmd-preview-section-18" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment">#</span><br><span class="hljs-comment"># 当前虚拟机2 MyServer2</span><br><span class="hljs-comment"># 上面测试中我们没有使用--ssl参数，也是用了ssl登录的，原因如下</span><br><span class="hljs-comment">#</span><br>[root@MyServer2 bin]&gt; ./mysql --help | grep ssl                <br>  --ssl               If <span class="hljs-built_in">set</span> to ON, this option enforces that SSL is<br>                      server. To <span class="hljs-built_in">disable</span> client SSL capabilities use --ssl=OFF.<br>                      (Defaults to on; use --skip-ssl to disable.)<br>                      <span class="hljs-comment"># 这里说，默认是开启的，可以用--skip-ssl 禁用</span><br></code></pre>

</div><div id="wmd-preview-section-19" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment">#</span><br><span class="hljs-comment"># 当前虚拟机2 MyServer2 </span><br><span class="hljs-comment"># 禁用ssl登录测试</span><br><span class="hljs-comment">#</span><br>[root@MyServer2 bin]&gt; ./mysql -u burn -h <span class="hljs-number">172.18</span>.<span class="hljs-number">14.68</span> -P3307 -p --skip-ssl  <span class="hljs-comment">#这里跳过了ssl</span><br>Enter password: <br>Welcome to the MySQL monitor.  Commands end with ; or \g.<br>Your MySQL connection id is <span class="hljs-number">7</span><br>Server version: <span class="hljs-number">5.7</span>.<span class="hljs-number">9</span>-log MySQL Community Server (GPL)<br><br>Copyright (c) <span class="hljs-number">2000</span>, <span class="hljs-number">2015</span>, Oracle and/or its affiliates. All rights reserved.<br><br>Oracle is a registered trademark of Oracle Corporation and/or its<br>affiliates. Other names may be trademarks of their respective<br>owners.<br><br>Type <span class="hljs-string">'help;'</span> or <span class="hljs-string">'\h'</span> <span class="hljs-keyword">for</span> help. Type <span class="hljs-string">'\c'</span> to clear the current input statement.<br><br>mysql&gt; \s<br>--------------<br>./mysql  Ver <span class="hljs-number">14.14</span> Distrib <span class="hljs-number">5.7</span>.<span class="hljs-number">9</span>, <span class="hljs-keyword">for</span> linux-glibc2.<span class="hljs-number">5</span> (x86_64) using  EditLine wrapper<br><br>Connection id:          <span class="hljs-number">7</span><br>Current database:<br>Current user:           burn@<span class="hljs-number">192.168</span>.<span class="hljs-number">115.223</span><br>SSL:                    Not <span class="hljs-keyword">in</span> use    <span class="hljs-comment"># 果然就禁用了ssl</span><br>Current pager:          stdout<br>Using outfile:          <span class="hljs-string">''</span><br>Using delimiter:        ;<br>Server version:         <span class="hljs-number">5.7</span>.<span class="hljs-number">9</span>-log MySQL Community Server (GPL)<br>Protocol version:       <span class="hljs-number">10</span><br>Connection:             <span class="hljs-number">172.18</span>.<span class="hljs-number">14.68</span> via TCP/IP<br>Server characterset:    utf8mb4<br>Db     characterset:    utf8mb4<br>Client characterset:    utf8<br>Conn.  characterset:    utf8<br>TCP port:               <span class="hljs-number">3307</span><br>Uptime:                 <span class="hljs-number">5</span> min <span class="hljs-number">50</span> sec<br><br>Threads: <span class="hljs-number">2</span>  Questions: <span class="hljs-number">24</span>  Slow queries: <span class="hljs-number">0</span>  Opens: <span class="hljs-number">109</span>  Flush tables: <span class="hljs-number">1</span>  Open tables: <span class="hljs-number">102</span>  Queries per second avg: <span class="hljs-number">0.068</span><br>--------------<br></code></pre>

</div><div id="wmd-preview-section-20" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 当前虚拟机1 MyServer, 当前实例mysql1</span><br><span class="hljs-comment">-- 让用户必须使用ssl</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"port"</span>;</span><br>+<span class="hljs-comment">---------------+-------+</span><br>| Variable_name | Value |<br>+<span class="hljs-comment">---------------+-------+</span><br>| port          | 3307  |<br>+<span class="hljs-comment">---------------+-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">alter</span> <span class="hljs-keyword">user</span> <span class="hljs-string">'burn'</span>@<span class="hljs-string">'%'</span> require ssl;</span><br>Query OK, 0 rows affected (0.02 sec)<br></code></pre>

</div><div id="wmd-preview-section-21" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment">#</span><br><span class="hljs-comment"># 当前虚拟机2 MyServer2</span><br><span class="hljs-comment">#</span><br>[root@MyServer2 bin]&gt; ./mysql -u burn -h <span class="hljs-number">172.18</span>.<span class="hljs-number">14.68</span> -P3307 -p --skip-ssl<br>Enter password: <br>ERROR <span class="hljs-number">1045</span> (<span class="hljs-number">28000</span>): Access denied <span class="hljs-keyword">for</span> user <span class="hljs-string">'burn'</span>@<span class="hljs-string">'192.168.115.223'</span> (using password: YES) <span class="hljs-comment">## 禁用了SSL就无法登录了</span><br><span class="hljs-comment">##</span><br>[root@MyServer2 bin]&gt; ./mysql -u burn -h <span class="hljs-number">172.18</span>.<span class="hljs-number">14.68</span> -P3307 -p <span class="hljs-comment"># 默认就启用ssl</span><br>Enter password: <br>Welcome to the MySQL monitor.  Commands end with ; or \g.<br>Your MySQL connection id is <span class="hljs-number">9</span><br>Server version: <span class="hljs-number">5.7</span>.<span class="hljs-number">9</span>-log MySQL Community Server (GPL)<br><br>Copyright (c) <span class="hljs-number">2000</span>, <span class="hljs-number">2015</span>, Oracle and/or its affiliates. All rights reserved.<br><br>Oracle is a registered trademark of Oracle Corporation and/or its<br>affiliates. Other names may be trademarks of their respective<br>owners.<br><br>Type <span class="hljs-string">'help;'</span> or <span class="hljs-string">'\h'</span> <span class="hljs-keyword">for</span> help. Type <span class="hljs-string">'\c'</span> to clear the current input statement.<br><br>mysql&gt; \s<br>--------------<br>./mysql  Ver <span class="hljs-number">14.14</span> Distrib <span class="hljs-number">5.7</span>.<span class="hljs-number">9</span>, <span class="hljs-keyword">for</span> linux-glibc2.<span class="hljs-number">5</span> (x86_64) using  EditLine wrapper<br><br>Connection id:          <span class="hljs-number">9</span><br>Current database:<br>Current user:           burn@<span class="hljs-number">192.168</span>.<span class="hljs-number">115.223</span><br>SSL:                    Cipher <span class="hljs-keyword">in</span> use is DHE-RSA-AES256-SHA  <span class="hljs-comment"># 确实启用了</span><br>Current pager:          stdout<br>Using outfile:          <span class="hljs-string">''</span><br>Using delimiter:        ;<br>Server version:         <span class="hljs-number">5.7</span>.<span class="hljs-number">9</span>-log MySQL Community Server (GPL)<br>Protocol version:       <span class="hljs-number">10</span><br>Connection:             <span class="hljs-number">172.18</span>.<span class="hljs-number">14.68</span> via TCP/IP<br>Server characterset:    utf8mb4<br>Db     characterset:    utf8mb4<br>Client characterset:    utf8<br>Conn.  characterset:    utf8<br>TCP port:               <span class="hljs-number">3307</span><br>Uptime:                 <span class="hljs-number">14</span> min <span class="hljs-number">25</span> sec<br><br>Threads: <span class="hljs-number">2</span>  Questions: <span class="hljs-number">32</span>  Slow queries: <span class="hljs-number">0</span>  Opens: <span class="hljs-number">109</span>  Flush tables: <span class="hljs-number">1</span>  Open tables: <span class="hljs-number">102</span>  Queries per second avg: <span class="hljs-number">0.036</span><br>--------------<br></code></pre>

</div><div id="wmd-preview-section-22" class="wmd-preview-section preview-content">

<h3 id="2-开启证书认证579">2. 开启证书认证(5.7.9)</h3>

</div><div id="wmd-preview-section-23" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 当前虚拟机1 MyServer， 当前实例 msyql1</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"port"</span>;</span><br>+<span class="hljs-comment">---------------+-------+</span><br>| Variable_name | Value |<br>+<span class="hljs-comment">---------------+-------+</span><br>| port          | 3307  |<br>+<span class="hljs-comment">---------------+-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">user</span> <span class="hljs-string">'burn_x509'</span>@<span class="hljs-string">'%'</span> <span class="hljs-keyword">identified</span> <span class="hljs-keyword">by</span> <span class="hljs-string">'123'</span> require x509;</span> <span class="hljs-comment">-- 启用证书认证</span><br>Query OK, 0 rows affected (0.02 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">grant</span> <span class="hljs-keyword">all</span> <span class="hljs-keyword">on</span> *.* <span class="hljs-keyword">to</span> <span class="hljs-string">'burn'</span>@<span class="hljs-string">'%'</span>;</span><br>Query OK, 0 rows affected (0.01 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> mysql.<span class="hljs-keyword">user</span> <span class="hljs-keyword">where</span> <span class="hljs-keyword">user</span>=<span class="hljs-string">'burn_x509'</span>\G<br>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br>                  Host: %<br>                  <span class="hljs-keyword">User</span>: burn_x509<br>           Select_priv: N<br>           Insert_priv: N<br>           Update_priv: N<br>           Delete_priv: N<br>           Create_priv: N<br>             Drop_priv: N<br>           Reload_priv: N<br>         Shutdown_priv: N<br>          Process_priv: N<br>             File_priv: N<br>            Grant_priv: N<br>       References_priv: N<br>            Index_priv: N<br>            Alter_priv: N<br>          Show_db_priv: N<br>            Super_priv: N<br> Create_tmp_table_priv: N<br>      Lock_tables_priv: N<br>          Execute_priv: N<br>       Repl_slave_priv: N<br>      Repl_client_priv: N<br>      Create_view_priv: N<br>        Show_view_priv: N<br>   Create_routine_priv: N<br>    Alter_routine_priv: N<br>      Create_user_priv: N<br>            Event_priv: N<br>          Trigger_priv: N<br>Create_tablespace_priv: N<br>              ssl_type: X509    <span class="hljs-comment">-- 使用X509登录</span><br>            ssl_cipher: <br>           x509_issuer: <br>          x509_subject: <br>         max_questions: <span class="hljs-number">0</span><br>           max_updates: <span class="hljs-number">0</span><br>       max_connections: <span class="hljs-number">0</span><br>  max_user_connections: <span class="hljs-number">0</span><br>                <span class="hljs-keyword">plugin</span>: mysql_native_password<br> authentication_string: *<span class="hljs-number">23</span>AE809DDACAF96AF0FD78ED04B6A265E05AA257<br>      password_expired: N<br> password_last_changed: <span class="hljs-number">2015</span>-<span class="hljs-number">11</span>-<span class="hljs-number">26</span> <span class="hljs-number">10</span>:<span class="hljs-number">14</span>:<span class="hljs-number">43</span><br>     password_lifetime: <span class="hljs-literal">NULL</span><br>        account_locked: N<br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

</div><div id="wmd-preview-section-24" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment">#</span><br><span class="hljs-comment"># 当前虚拟机2 MyServer2 </span><br><span class="hljs-comment">#</span><br>[root@MyServer2 bin]&gt; ./mysql -u burn_x509 -h <span class="hljs-number">172.18</span>.<span class="hljs-number">14.68</span> -P3307 -p<br>Enter password: <br>ERROR <span class="hljs-number">1045</span> (<span class="hljs-number">28000</span>): Access denied <span class="hljs-keyword">for</span> user <span class="hljs-string">'burn_x509'</span>@<span class="hljs-string">'192.168.115.223'</span> (using password: YES)  <span class="hljs-comment"># 即使默认开启了ssl，也是无法登录的</span><br></code></pre>

</div><div id="wmd-preview-section-25" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment">#</span><br><span class="hljs-comment"># 当前虚拟机1 MyServer</span><br><span class="hljs-comment">#</span><br>[root@MyServer data1]&gt; <span class="hljs-built_in">pwd</span><br>/data1<br>[root@MyServer data1]&gt; ll | grep pem<br>-rw-------. <span class="hljs-number">1</span> mysql mysql      <span class="hljs-number">1675</span> Nov <span class="hljs-number">25</span> <span class="hljs-number">23</span>:<span class="hljs-number">55</span> ca-key.pem<br>-rw-r--r--. <span class="hljs-number">1</span> mysql mysql      <span class="hljs-number">1070</span> Nov <span class="hljs-number">25</span> <span class="hljs-number">23</span>:<span class="hljs-number">55</span> ca.pem<br>-rw-r--r--. <span class="hljs-number">1</span> mysql mysql      <span class="hljs-number">1078</span> Nov <span class="hljs-number">25</span> <span class="hljs-number">23</span>:<span class="hljs-number">55</span> client-cert.pem<br>-rw-------. <span class="hljs-number">1</span> mysql mysql      <span class="hljs-number">1679</span> Nov <span class="hljs-number">25</span> <span class="hljs-number">23</span>:<span class="hljs-number">55</span> client-key.pem<br>-rw-------. <span class="hljs-number">1</span> mysql mysql      <span class="hljs-number">1675</span> Nov <span class="hljs-number">25</span> <span class="hljs-number">23</span>:<span class="hljs-number">55</span> private_key.pem<br>-rw-r--r--. <span class="hljs-number">1</span> mysql mysql       <span class="hljs-number">451</span> Nov <span class="hljs-number">25</span> <span class="hljs-number">23</span>:<span class="hljs-number">55</span> public_key.pem<br>-rw-r--r--. <span class="hljs-number">1</span> mysql mysql      <span class="hljs-number">1078</span> Nov <span class="hljs-number">25</span> <span class="hljs-number">23</span>:<span class="hljs-number">55</span> server-cert.pem<br>-rw-------. <span class="hljs-number">1</span> mysql mysql      <span class="hljs-number">1679</span> Nov <span class="hljs-number">25</span> <span class="hljs-number">23</span>:<span class="hljs-number">55</span> server-key.pem<br>[root@MyServer data1]&gt; scp client-cert.pem  client-key.pem  root@<span class="hljs-number">172.18</span>.<span class="hljs-number">14.41</span>:~/<br>The authenticity of host <span class="hljs-string">'172.18.14.41 (172.18.14.41)'</span> can<span class="hljs-string">'t be established.<br>RSA key fingerprint is 5f:f5:3c:b0:57:79:8d:50:c6:c8:69:b0:90:6e:98:3b.<br>Are you sure you want to continue connecting (yes/no)? yes<br>Warning: Permanently added '</span><span class="hljs-number">172.18</span>.<span class="hljs-number">14.41</span><span class="hljs-string">' (RSA) to the list of known hosts.<br>root@172.18.14.41'</span>s password: <br>client-cert.pem                                                                                           <span class="hljs-number">100</span>% <span class="hljs-number">1078</span>     <span class="hljs-number">1.1</span>KB/s   <span class="hljs-number">00</span>:<span class="hljs-number">00</span>    <br>client-key.pem                                                                                            <span class="hljs-number">100</span>% <span class="hljs-number">1679</span>     <span class="hljs-number">1.6</span>KB/s   <span class="hljs-number">00</span>:<span class="hljs-number">00</span>    <br></code></pre>

</div><div id="wmd-preview-section-26" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment">#</span><br><span class="hljs-comment"># 当前虚拟机2 MyServer2</span><br><span class="hljs-comment">#</span><br>[root@MyServer2 ~]&gt; ll | grep pem<br>-rw-r--r--.  <span class="hljs-number">1</span> root root       <span class="hljs-number">1078</span> Nov <span class="hljs-number">26</span> <span class="hljs-number">10</span>:<span class="hljs-number">22</span> client-cert.pem<br>-rw-------.  <span class="hljs-number">1</span> root root       <span class="hljs-number">1679</span> Nov <span class="hljs-number">26</span> <span class="hljs-number">10</span>:<span class="hljs-number">22</span> client-key.pem<br><br>[root@MyServer2 ~]&gt; mysql -u burn_x509 -h <span class="hljs-number">172.18</span>.<span class="hljs-number">14.68</span> -P <span class="hljs-number">3307</span> -p --ssl-cert=./client-cert.pem  --ssl-key=./client-key.pem <br>Enter password: <br>Welcome to the MySQL monitor.  Commands end with ; or \g.<br>Your MySQL connection id is <span class="hljs-number">12</span><br>Server version: <span class="hljs-number">5.7</span>.<span class="hljs-number">9</span>-log MySQL Community Server (GPL)<br><br>Copyright (c) <span class="hljs-number">2000</span>, <span class="hljs-number">2015</span>, Oracle and/or its affiliates. All rights reserved.<br><br>Oracle is a registered trademark of Oracle Corporation and/or its<br>affiliates. Other names may be trademarks of their respective<br>owners.<br><br>Type <span class="hljs-string">'help;'</span> or <span class="hljs-string">'\h'</span> <span class="hljs-keyword">for</span> help. Type <span class="hljs-string">'\c'</span> to clear the current input statement.<br><br>mysql&gt; \s<br>--------------<br>mysql  Ver <span class="hljs-number">14.14</span> Distrib <span class="hljs-number">5.6</span>.<span class="hljs-number">27</span>, <span class="hljs-keyword">for</span> linux-glibc2.<span class="hljs-number">5</span> (x86_64) using  EditLine wrapper<br><br>Connection id:          <span class="hljs-number">12</span><br>Current database:<br>Current user:           burn_x509@<span class="hljs-number">192.168</span>.<span class="hljs-number">115.223</span><br>SSL:                    Cipher <span class="hljs-keyword">in</span> use is DHE-RSA-AES256-SHA <span class="hljs-comment"># 使用加密方式登录，且通过证书，因为这个用户 require X509</span><br>Current pager:          stdout<br>Using outfile:          <span class="hljs-string">''</span><br>Using delimiter:        ;<br>Server version:         <span class="hljs-number">5.7</span>.<span class="hljs-number">9</span>-log MySQL Community Server (GPL)<br>Protocol version:       <span class="hljs-number">10</span><br>Connection:             <span class="hljs-number">172.18</span>.<span class="hljs-number">14.68</span> via TCP/IP<br>Server characterset:    utf8mb4<br>Db     characterset:    utf8mb4<br>Client characterset:    utf8<br>Conn.  characterset:    utf8<br>TCP port:               <span class="hljs-number">3307</span><br>Uptime:                 <span class="hljs-number">32</span> min <span class="hljs-number">15</span> sec<br><br>Threads: <span class="hljs-number">2</span>  Questions: <span class="hljs-number">41</span>  Slow queries: <span class="hljs-number">0</span>  Opens: <span class="hljs-number">114</span>  Flush tables: <span class="hljs-number">1</span>  Open tables: <span class="hljs-number">107</span>  Queries per second avg: <span class="hljs-number">0.021</span><br>--------------<br></code></pre></div><div id="wmd-preview-section-footnotes" class="preview-content"></div></div></body></html>